package com.eorchis.module.predeptassignedinfo.dao.impl;

import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.module.TrainingConstants;
import com.eorchis.module.predeptassignedinfo.dao.IPreDeptAssignedInfoDao;
import com.eorchis.module.predeptassignedinfo.domain.PreDeptAssignedInfo;
import com.eorchis.module.predeptassignedinfo.domain.PreDeptAssignedInfoBean;

import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;

import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;

import com.eorchis.module.predeptassignedinfo.ui.commond.PreDeptAssignedInfoQueryCommond;
import com.eorchis.module.predeptassignedinfo.ui.commond.PreDeptAssignedInfoValidCommond;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: PreDeptAssignedInfoDaoImpl<br>
 * Description: 单位年度分配参学预授权名额表数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author Administrator
 * @createDate 2015-01-25
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.predeptassignedinfo.dao.impl.PreDeptAssignedInfoDaoImpl")
public class PreDeptAssignedInfoDaoImpl extends HibernateAbstractBaseDao implements IPreDeptAssignedInfoDao {
	
	@Override
	public Class<? extends IBaseEntity> entityClass() {
		return PreDeptAssignedInfo.class;
	}

	@Override
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		PreDeptAssignedInfoQueryCommond preDeptAssignedInfoQueryCommond = (PreDeptAssignedInfoQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM PreDeptAssignedInfo t");
		defaultQueryConditionBuilder.addCondition("t.id", CompareType.IN, preDeptAssignedInfoQueryCommond.getSearchIds());
	}
	/**
	 * 查询分配参学名额列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @date 2015-1-25
	 */
	@Override
	public List<PreDeptAssignedInfoBean> findPreDeptAssignedInfoList(
			PreDeptAssignedInfoQueryCommond queryCommond) throws Exception {
		List<PreDeptAssignedInfoBean> result=null;
		//拼写查询的sql
		String sql="select r.id as \"id\",r.year as \"year\",r.restrict_person_num as \"restrictPersonNum\",r.class_type as \"classType\",r.depid as \"deptId\",r.depname as \"deptName\",r.deptUserNum as \"deptUserNum\" " +
				" from (select t.id,t.year,t.restrict_person_num,t.class_type,t.depid,bd.depname,(select count(u.userid) from base_department_user du, base_department bt, base_user u " +
				" where bt.depid = du.depid and du.userid = u.userid and du.active_state="+TrainingConstants.IS_ACTIVE_Y+" and u.active_state="+TrainingConstants.IS_ACTIVE_Y+""+
				" and bt.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = t.depid),'%')) as deptUserNum " +//部门人数(包括下级部门的人数)
				" from PRE_DEPT_ASSIGNED_INFO t left join signup_dept bd on t.depid=bd.depid where 1=1 ";
		//拼写查询条件sql
		Map<String, Object> values = new HashMap<String, Object>();
		//按照培训班类型查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			sql+=" and t.class_type =:classType ";
			values.put("classType", queryCommond.getSearchClassType());
		}
		//按照单位id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())){
			sql+=" and t.depid=:depid ";
			values.put("depid", queryCommond.getSearchDepId());
		}
		//按照单位名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepname())){
			sql+=" and bd.depname like :depName ";
			values.put("depName", "%"+queryCommond.getSearchDepname().trim()+"%");
		}
		//按照年份查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYear())){
			sql+=" and t.year =:year ";
			values.put("year", queryCommond.getSearchYear());
		}
		sql+=") r ";
		//拼写排序的
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			sql+=" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection());
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(PreDeptAssignedInfoBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		result = (List<PreDeptAssignedInfoBean>)this.findList(queryCondition, firstResult, queryCommond.getLimit());
		return result;
	}
	/**
	 * 修改预分配参学名额列表上的限定人数
	 * @param validCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2015-1-25
	 */
	@Override
	public boolean updatePreDeptAssignedInfo(
			PreDeptAssignedInfoValidCommond validCommond) throws Exception {
		boolean result = false;
		String hql = "update PRE_DEPT_ASSIGNED_INFO c set c.RESTRICT_PERSON_NUM=:restrictPersonNum where c.ID=:id ";
		Map<String,Object> params = new Hashtable<String,Object>();
		params.put("id", validCommond.getId());
		params.put("restrictPersonNum", validCommond.getRestrictPersonNum());
		int u  = this.executeUpdate(QueryStringType.SQL, hql.toString(), params);
		if (u == 1) {
			result = true;
		}
		return result;
	}
	/**
	 * 预分配参学名额:未选单位列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @date 2015-1-25
	 */
	@Override
	public List<PreDeptAssignedInfoBean> findNoSelectSignupDeptList(
			PreDeptAssignedInfoQueryCommond queryCommond) throws Exception {
		List<PreDeptAssignedInfoBean> result=null;
		Map<String, Object> values = new HashMap<String, Object>();
		//拼写查询的sql
		String sql="select r.depid as \"deptId\",r.depname as \"deptName\",r.parentDeptName as \"parentDeptName\",r.deptUserNum as \"deptUserNum\" " +
				" from (select t.depid,t.depname,(select count(u.userid) from base_department_user du, base_department bt, base_user u " +
				" where bt.depid = du.depid and du.userid = u.userid and du.active_state ="+TrainingConstants.IS_ACTIVE_Y+" and u.active_state ="+TrainingConstants.IS_ACTIVE_Y+""+
				" and bt.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = t.depid),'%')) as deptUserNum," +//部门人数(包括下级部门的人数)
				" (select sd.depname from base_department sd  where sd.depid = t.parent_id) as parentDeptName" +//父级部门名称
				" from signup_dept t where t.depid not in (select di.depid from dept_assigned_info di where 1=1 and di.year =:year and di.class_type =:classType)" +//去掉已经选入单位年度参学名额表中的部门
				" and t.depid not in (select di.depid from pre_dept_assigned_info di where 1 = 1 and di.year =:year2 and di.class_type =:classType2) ";//去掉已经选入单位年度预参学名额表中的部门
		//年份
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYear())){
			values.put("year", queryCommond.getSearchYear());
			values.put("year2", queryCommond.getSearchYear());
		}
		//培训班类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			values.put("classType", queryCommond.getSearchClassType());
			values.put("classType2", queryCommond.getSearchClassType());
		}
		//按照父级部门ID查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchParentId())){
			sql+=" and t.parent_id =:parentId ";
			values.put("parentId", queryCommond.getSearchParentId());
		}
		//按照部门名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepname())){
			sql+=" and t.depname like :depname ";
			values.put("depname", "%"+queryCommond.getSearchDepname().trim()+"%");
		}
		sql+=") r ";
		//拼写排序的
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			sql+=" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection());
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(PreDeptAssignedInfoBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		result = (List<PreDeptAssignedInfoBean>)this.findList(queryCondition, firstResult, queryCommond.getLimit());
		return result;
	}
}
